import requests
from requests.packages import urllib3
import json
import pymysql


db = pymysql.connect("localhost","root","root","free" )
cursor = db.cursor()
# cursor.execute("SELECT VERSION()")
# data = cursor.fetchone()
# print ("Database version : %s " % data)


urllib3.disable_warnings()
data={'rp':50,'listed':'Y'}
req = requests.post('https://www.jisilu.cn/data/cbnew/cb_list/',data=data,verify=False)
print(req)
js=json.loads(req.text)
rows=js['rows']
print(js['total'])
#print(js['rows'][0])
#print(js['rows'][0]['cell']['bond_id'])
#print(js['rows'][0]['cell']['bond_nm'])
# for row in rows[:10]:
for row in rows:
    cell=row['cell']
    zzdm=cell['bond_id']
    print('转债代码= %s' % zzdm)
    cursor.execute("SELECT * from t_kzz_fx t where t.`转债代码`= %s " % (zzdm))
    ysjl = cursor.fetchone()
    if ysjl:
         cursor.execute("update  t_kzz_fx t set t.`现价`='%s',t.`涨跌幅`='%s',t.`正股价`='%s',t.`正股涨跌`='%s',t.PB='%s' \
             ,t.`转股价`='%s',t.`转股价值`='%s',t.`溢价率`='%s',t.`纯债价值`='%s',t.`评级`='%s' \
             ,t.`期权价值`='%s',t.`回售触发价`='%s',t.`强赎触发价`='%s',t.`转债占比`='%s',t.`机构持仓`='%s' \
             ,t.`到期时间`='%s',t.`剩余年限`='%s',t.`剩余规模`='%s',t.`成交额`='%s',t.`换手率`='%s' \
             ,t.`到期税前收益`='%s',t.`回售收益`='%s',t.`双低`='%s',t.`convert_cd_tip`='%s',t.`force_redeem`='%s' \
             where t.`转债代码`= '%s' " % 
            ( cell['price'],cell['increase_rt'],cell['sprice'],cell['sincrease_rt'],cell['pb'],
             cell['convert_price'],cell['convert_value'],cell['premium_rt'],cell['bond_value'],cell['rating_cd'],
             'buy',cell['put_convert_price'],cell['force_redeem_price'],cell['convert_amt_ratio'],cell['fund_rt'],
             cell['short_maturity_dt'],cell['year_left'],cell['curr_iss_amt'],cell['volume'],cell['turnover_rt'],
             cell['ytm_rt'],'**',cell['dblow'],cell['convert_cd_tip'],cell['force_redeem'],
             zzdm))
    else:
        cursor.execute("INSERT INTO `t_kzz_fx` (`转债代码`, `转债名称`, `现价`, `涨跌幅`, `正股名称`, \
         `正股价`, `正股涨跌`, `PB`, `转股价`, `转股价值`, \
         `溢价率`, `纯债价值`, `评级`, `期权价值`, `回售触发价`, \
         `强赎触发价`, `转债占比`, `机构持仓`, `到期时间`, `剩余年限`, \
         `剩余规模`, `成交额`, `换手率`, `到期税前收益`, `回售收益`, \
         `双低`,convert_cd_tip,force_redeem) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" 
         % ( zzdm, cell['bond_nm'],cell['price'],cell['increase_rt'],cell['stock_nm'],
         cell['sprice'],cell['sincrease_rt'],cell['pb'], cell['convert_price'],cell['convert_value'],
         cell['premium_rt'],cell['bond_value'],cell['rating_cd'],'',cell['put_convert_price'],
         cell['force_redeem_price'],cell['convert_amt_ratio'],cell['fund_rt'],cell['short_maturity_dt'],cell['year_left'],
         cell['curr_iss_amt'],cell['volume'],cell['turnover_rt'],cell['ytm_rt'],'**',
         cell['dblow'],cell['convert_cd_tip'],cell['force_redeem']))



db.commit()

db.close()